﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using DBUtility;

namespace OracleDAL.ElecBoss
{
    public class DutyZbrj
    {
        public DataSet getDutyInfo(string userisn,string nodeid)
        {
            DataSet ds = new DataSet();
            string strSql = "select dutyid,userisn,tzzs,rcxj,ywcl,gzgd,qt,to_char(starttime,'yyyy-mm-dd hh24:mi:ss') as starttime,to_char(endtime,'yyyy-mm-dd hh24:mi:ss') as endtime  from duty_info";
            strSql += " where userisn='" + userisn + "' ";
            //strSql+="and  to_char(starttime,'yyyy-mm-dd')= to_char ((sysdate), 'yyyy-mm-dd') ";
            strSql += "and  nodeid='" + nodeid + "'  and isduty='1'";
            ds = OracleHelper.Query(strSql);
            return ds;
        }
        /// <summary>
        /// 获取用户是否是在线用户
        /// </summary>
        /// <param name="userisn">用户ID</param>
        /// <returns></returns>
        public DataSet IsUserOnline(string userisn)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select USERISN from ONLINE_USER ");
            sb.Append(" where USERISN='" + userisn + "' ");
            sb.Append(" and DUTYTYPE='1' and round(to_number(sysdate-REPORTSTATETIME)*1440*60)<=6000 ");
            return OracleHelper.Query(sb.ToString());
        }
        public DataSet  getDutyCount()
        {
            DataSet ds = new DataSet();
            string strSql = "select dutyid,userisn,tzzs,rcxj,ywcl,gzgd,qt,to_char(starttime,'yyyy-mm-dd hh24:mi:ss') as starttime,to_char(endtime,'yyyy-mm-dd hh24:mi:ss') as endtime  from duty_info ";
            strSql += " where  to_char(starttime,'yyyy-mm-dd')= to_char ((sysdate), 'yyyy-mm-dd') ";
            strSql += "and  nodeid='9701' and isduty='1'";
            ds = DBUtility.OracleHelper.Query(strSql);
            return ds;
        }
        
        public DataSet getDutyFromDutyID(string dutyID)
        {
            DataSet ds = new DataSet();
            string strSql = "select dutyid,userisn,tzzs,rcxj,ywcl,gzgd,qt,starttime,endtime from duty_info where dutyid='" + dutyID + "'";
            ds = DBUtility.OracleHelper.Query(strSql);
            return ds;
        
        }
        public int addDuty(string id, string field,string value,string nodeid,string userisn)
        {
            string strSql = "insert into duty_info (dutyid,starttime," + field + ",nodeid,userisn,isduty) values('" + id + "',sysdate,'" + value + "','" + nodeid + "','" + userisn + "','1')";
            int result = DBUtility.OracleHelper.ExecuteSql(strSql);
            return result;
        }
        public int editDuty(string id, string field, string value)
        {
            if (field != "endtime" && field != "starttime")
            {
                value = "'" + value + "'";
            }
            string strSql = "update duty_info set " + field + "=" + value + " where dutyid='"+ id +"'";
            int result = DBUtility.OracleHelper.ExecuteSql(strSql);
            return result;
        }
        public DataSet nextAndLastDuty(string type,string date)
        {
            string strSql = "";
            strSql = "select DUTYID,USERISN,TZZS,RCXJ,YWCL,GZGD,STARTTIME,ENDTIME,QT from ";
            if (type == "last")
            {
                //strSql += "(select  DUTYID,USERISN,TZZS,RCXJ,YWCL,GZGD,STARTTIME,ENDTIME,QT from duty_info where starttime>" + date + "  and isduty='1') where rownum<3";
                //strSql += "(select  DUTYID,USERISN,TZZS,RCXJ,YWCL,GZGD,STARTTIME,ENDTIME,QT from duty_info where starttime>" + date + "  and isduty='1') ";
                strSql += "(select  DUTYID,USERISN,TZZS,RCXJ,YWCL,GZGD,STARTTIME,ENDTIME,QT from duty_info where starttime>" + date + " ) ";
                strSql += "order by  starttime asc";
            }
            else
            {
                //strSql += "(select  DUTYID,USERISN,TZZS,RCXJ,YWCL,GZGD,STARTTIME,ENDTIME,QT from duty_info where starttime<" + date + "  and isduty='1') where rownum<3 ";
                //strSql += "(select  DUTYID,USERISN,TZZS,RCXJ,YWCL,GZGD,STARTTIME,ENDTIME,QT from duty_info where starttime<" + date + "  and isduty='1') ";
                strSql += "(select  DUTYID,USERISN,TZZS,RCXJ,YWCL,GZGD,STARTTIME,ENDTIME,QT from duty_info where starttime<" + date + " ) ";
                strSql += "order by  starttime desc";
            }
            DataSet ds = new DataSet();
            ds = DBUtility.OracleHelper.Query(strSql);
            return ds;
        }
        public DataSet nextAndLastDutydetail(string type, string date)
        {
            string strSql = "";
            strSql = " select DUTYID,USERISN,TZZS,RCXJ,YWCL,GZGD,STARTTIME,ENDTIME,QT,USERNAME,TEL from  ";
            if (type == "last")
            {
              //  strSql += " (select duty.DUTYID,duty.USERISN,duty.TZZS,duty.RCXJ,duty.YWCL,duty.GZGD,duty.STARTTIME,duty.ENDTIME,duty.QT,u.username,u.tel  from duty_info  duty,user_info  u where   duty.userisn=u.userisn and starttime>" + date + "  and isduty='1') where rownum<3";
                strSql += " (select duty.DUTYID,duty.USERISN,duty.TZZS,duty.RCXJ,duty.YWCL,duty.GZGD,duty.STARTTIME,duty.ENDTIME,duty.QT,u.username,u.tel  from duty_info  duty,user_info  u where   duty.userisn=u.userisn and starttime>" + date + "  and isduty='1') ";
                strSql += "order by  starttime asc";
            }
            else
            {
               // strSql += " (select duty.DUTYID,duty.USERISN,duty.TZZS,duty.RCXJ,duty.YWCL,duty.GZGD,duty.STARTTIME,duty.ENDTIME,duty.QT,u.username,u.tel  from duty_info  duty,user_info  u where   duty.userisn=u.userisn and starttime<" + date + "  and isduty='1') where rownum<3";
                strSql += " (select duty.DUTYID,duty.USERISN,duty.TZZS,duty.RCXJ,duty.YWCL,duty.GZGD,duty.STARTTIME,duty.ENDTIME,duty.QT,u.username,u.tel  from duty_info  duty,user_info  u where   duty.userisn=u.userisn and starttime<" + date + "  and isduty='1') ";
                strSql += "order by  starttime desc";
            }
            DataSet ds = new DataSet();
            ds = DBUtility.OracleHelper.Query(strSql);
            return ds;
        }
        public DataSet  impStr()
        {
            string strSql = "select impcontent,id from duty_import   order by inputtime";
            DataSet ds = new DataSet();
            return DBUtility.OracleHelper.Query(strSql);

        }
        public DataSet getThingInfo()
        {
            string strSql = "select userthing_info.thingcontent,userisn,(select username from user_info where userisn=userthing_info.userisn) as username from userthing_info ";
            return DBUtility.OracleHelper.Query(strSql);
        }
        /// <summary>
        /// 删除值班员 出现故障过长时间将删除
        /// </summary>
        /// <returns></returns>
        public int delLedayDuty()
        {
            string strSql = "delete online_user where  sysdate-REPORTSTATETIME >1/24";
            return DBUtility.OracleHelper.ExecuteSql(strSql);
        }
        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <returns></returns>
        public int ExcuteSql(string strSql)
        {
            return DBUtility.OracleHelper.ExecuteSql(strSql);
        }
        public DataSet getDutySum()
        {
            //只有用户值班状态为值班，且状态更新时间小于6分钟则人为用户为正确的值班员
            return DBUtility.OracleHelper.Query("select userisn,dutytype,reportstatetime from online_user where dutytype='1' and round(to_number(sysdate-reportstatetime)*1440*60)<=6000");
        }
    }
}
